#! /usr/bin/perl 

#----------------------------------------------------------------
# Script to read Reconciliation input spreadsheet file. It can
# read *.xls(Excel) and *.xlsx(Excel 2007).
#
# The file type is determined on '/usr/bin/file' output.
#---------------------------------------------------------------- 

use strict;
use Spreadsheet::XLSX;
use Spreadsheet::ParseExcel;
use Date::Simple("date");
use PayorRptLabel("%Payor_Excel_labels");
use ReconRptLabel("%Recon_Excel_labels");


#-------------------------------------------
# Format Floating point currency num for printing.
#-------------------------------------------
sub to2dp { 
	my $money = shift;
	return "" if $money =~ /^(-|0|0.0|0.00)$/;
	return "" if $money =~ /^$/;
	my $neg = 0;
	$neg = -1 if ( $money =~ /\(/ ); 
	$money =~ s/[\$\)\(\,_]//g;
	$money =~ s/\s//g;
	$money *= -1.0 if $neg; 
	sprintf "%.2f", $money + 1e-8 ; 
}

#-------------------------------------------
# Format xls Date 1900  sequence num
#------------------------------------------
sub fmt_date($) {
	my $xls_date = shift;	
	my $d = $xls_date - 2 + date("1900-01-01");
	my $z = $d->strftime("%m/%d/%G" ); 
	return $z;
}


#-------------------------------------------
# Process report 'Comments' field content
#------------------------------------------
sub handle_comment($) {
	my $comm = shift;
	my $ret = $comm;

	$ret = "" if( $comm =~ /^\s*(paid|billed)\s*$/i ); 
	$ret = "" if( $comm =~ /^\s*paid\s+.*am.*paid\s*>?\s*\d+/i ); 
	$ret = "" if( $comm =~ /^\s*(re)?billed\s*:?\s+accepted\s*$/i ); 
	$ret = "" if( $comm =~ /^re:\s+billed\s+accepted\s*$/i ); 
	if ( length($comm) > 119 ) {
		$ret = substr($comm, 0, 119);		#varchar(120)
	}
	$ret =~ s/^\s*//;
	$ret =~ s/\s*$//;
	uc $ret;
}


#-----------------------------------------------
# get_fields(\%)
#   return the sorted report label hash keys
#-----------------------------------------------
sub get_fields($) {
	my $hrRptLabel = shift;
	my @flds = sort { $hrRptLabel->{$a}[0] 
				<=>  
			  $hrRptLabel->{$b}[0] 
			}
                    keys %$hrRptLabel;
	return @flds;
}


#---------------------------------------------------------------
# match_field_label(\%, key, report label)
#   return value: 
#       0  --  no matching predefined pattern. 
#       1  --  does not matter, Recon DB ignores it 
#       2  --  matched,      insert into Recon DB 
#---------------------------------------------------------------
sub match_field_label($$$) {
	my $hrRptLabel = shift;
	my $fld = shift;
	my $lbl = shift;

	my $ret=0;
	if( $hrRptLabel->{$fld}[2] =~ /^$/ ) {
		$ret = 1;
	}elsif( $lbl =~ /$hrRptLabel->{$fld}[2]/ ) {
		$ret = 2;
	}

	$ret;
}


#-------------------------------------------
# verify_Excel_fields(sheet, rpt_label_hash)
#
# Quick check the Excel data fields
#-------------------------------------------
sub verify_Excel_fields($$){
	my $sheet = shift;
	my $hrRptLabel = shift;

	my ($mr, $row_max ) = $sheet->row_range();
	my ($mc, $col_max ) = $sheet->col_range();

	return 0 if $row_max <0;;
	
	my @rptFlds = get_fields($hrRptLabel);

	my $ret=1;
	foreach my $i ( 0 .. $#rptFlds ) {
		if( $mc+$i > $col_max ) {
			$ret = 0; last;
		}
		my $fkey = $rptFlds[$i];
		my $cell = $sheet->get_cell($mr, $mc+$i);
		next if match_field_label($hrRptLabel, $fkey, $cell->value());
		$ret = 0;
		last;
	}
	$ret;
}

#-------------------------------------------
# verify_xlsx_fields(sheet, rpt_label_hash)
#
# Quick check the XLSX spreadsheet data fields
#-------------------------------------------
sub verify_xlsx_fields($$){
	my $sheet = shift;
	my $hrRptLabel = shift;

	return 0 if ! $sheet->{MaxRow};

	my @rptFlds = get_fields($hrRptLabel);

	my $ret=1;
	my $mr = $sheet->{MinRow};
	my $mc = $sheet->{MinCol};
	foreach my $i ( 0 .. $#rptFlds ) {
		if( $mc+$i > $sheet->{MaxCol} ) {
			$ret = 0; last;
		}
		my $fkey = $rptFlds[$i];
		next if match_field_label($hrRptLabel, $fkey, 
				$sheet->{Cells}[$mr][$mc+$i]->{Val}) ;
		$ret = 0;
		last;
	}
	$ret;
}


#------------------------------------------------------------
# Grab useful info from XLSX spreadsheet.
#
#   Two fields, 'data_flag' and 'our_run_date' are
# not from the spreadsheet.  
#       'data_flag' = 'C'
#       'our_run_date' is the recon file creation time.
#-----------------------------------------------------------
sub dump_Recon_Xlsx($$) {
	my $input = shift;
	my $Excel_labels = shift;

	my $ordate = $input;
	$ordate =~ s!^.*/!!;
	$ordate =~ s/^(\d{6}_\d{6}).*/$1/;
	my($dat, $hms) = split(/_/, $ordate);
	$dat =~ s!(\d\d)(\d\d)(\d\d)!$1/$2/$3!;
	$hms =~ s!(\d\d)(\d\d)(\d\d)!$1:$2:$3!;
	$ordate = "$dat $hms";

	my @Fields = get_fields($Excel_labels);

	my $excel_obj = Spreadsheet::XLSX->new($input, undef);
	foreach my $sheet (@{$excel_obj->{Worksheet}}) {
		verify_xlsx_fields($sheet,  $Excel_labels ) or next;	#select right sheet
		my $data_row = $sheet->{MinRow} +1;
		$sheet->{MaxRow} ||= $sheet->{MinRow};
		foreach my $row ($data_row .. $sheet->{MaxRow}) {
			my $record="C";     #insert the 'data_flag' value at front
			my $col = 0;
			foreach ( @Fields ) {
				if( match_field_label($Excel_labels, $_, "")==1 ){
					$col ++; next;
				}
				my $cell = $sheet->{Cells}[$row][$col+$sheet->{MinCol}];
				my $val  = $cell->{Val};
				$val =~ s/[^\x20-\x7e]//g;
				$val =~ s/[\|\"\\]//g;

				return if ( $col == 0 and $val !~ /\d/); #No more data row

				$val = to2dp($val)    if ($col == 6 or $col ==7 or $col==19);
				$val = fmt_date($val) if $cell->{Type} =~ /Date/;
				if( $col >=15 && $col <=18 ) {
					$val=~ s/\s//g;
					$val="" if $val !~ m{\d\d?[/-]\d\d?[/-]\d\d(\d\d)?$};	
				}
				$val = handle_comment($val) if $col==20;
				$record .= "|$val";

				if ( $col == 12  ) {		# insert 'our_run_date' after carrier_code
					$record .= "|$ordate";
				}
				elsif( $col == 19 and $val > 0  ) { # ignore comments field 
					$record .= "|";  last;
				}
				$col ++;
			}
			print "$record\n";
		}
	}
}


#------------------------------------------------------------
# Grab useful info from XLSX spreadsheet.
#
#   Two fields, 'data_flag' and 'our_run_date' are
# not from the spreadsheet.  
#       'data_flag' = 'C'
#       'our_run_date' is the recon file creation time.
#-----------------------------------------------------------
sub dump_Payor_Xlsx($$) {
	my $input = shift;
	my $Excel_labels = shift;

	my $ordate = $input;
	$ordate =~ s!^.*/!!;
	$ordate =~ s/^(\d{6}_\d{6}).*/$1/;
	my($dat, $hms) = split(/_/, $ordate);
	$dat =~ s!(\d\d)(\d\d)(\d\d)!$1/$2/$3!;
	$ordate = "$dat 00:00:00";

	my @Fields = get_fields($Excel_labels);

	my $excel_obj = Spreadsheet::XLSX->new($input, undef);
	foreach my $sheet (@{$excel_obj->{Worksheet}}) {
		verify_xlsx_fields($sheet,  $Excel_labels) or next;	#select right sheet
		my $data_row = $sheet->{MinRow} +1;
		$sheet->{MaxRow} ||= $sheet->{MinRow};
		foreach my $row ($data_row .. $sheet->{MaxRow}) {
			my $record="C";     #insert the 'data_flag' value at front
			my $col = 0;
			foreach ( @Fields ) {
				if( match_field_label($Excel_labels, $_, "")==1 ){
					$col ++; next;
				}
				my $cell = $sheet->{Cells}[$row][$col+$sheet->{MinCol}];
				my $val  = $cell->{Val};

				return if ( $col == 0 and $val !~ /\d/); #No more data row

				$val = to2dp($val)    if ($col == 6 or $col ==7 or $col==68);
				$val = fmt_date($val) if $cell->{Type} =~ /Date/;
				if( $col >=65 && $col <=67) {
					$val=~ s/\s//g;
					$val="" if $val !~ m{\d\d?[/-]\d\d?[/-]\d\d(\d\d)?$};	
				}
				$val = handle_comment($val) if $col==69;
				$record .= "|$val";

				if ( $col == 14  ) {		# insert 'our_run_date' after carrier_code
					$record .= "|$ordate";
				} 
				elsif ( $col == 67  ) {	# insert empty field for 'Prior Ins Date' 
					$record .= "|";
				}

				$col ++;
			}
			print "$record\n";
		}
	}
}


#------------------------------------------------------------
# Grab useful info from Excel.
#
#   Two fields, 'data_flag' and 'our_run_date' are
# not from the spreadsheet.  
#       'data_flag' = 'C'
#       'our_run_date' is the recon file creation time.
#-----------------------------------------------------------
sub dump_Recon_Excel($$) {
	my $input = shift;
	my $Excel_labels = shift;

	my $ordate = $input;
	$ordate =~ s!^.*/!!;
	$ordate =~ s/^(\d{6}_\d{6}).*/$1/;
	my($dat, $hms) = split(/_/, $ordate);
	$dat =~ s!(\d\d)(\d\d)(\d\d)!$1/$2/$3!;
	$hms =~ s!(\d\d)(\d\d)(\d\d)!$1:$2:$3!;
	$ordate = "$dat $hms";

	my @Fields = get_fields($Excel_labels);

	my $excel_obj = Spreadsheet::ParseExcel->new();
	my $workbook = $excel_obj->parse($input);
	foreach my $sheet ( $workbook->worksheets()) {
		verify_Excel_fields($sheet,  $Excel_labels) or next;	#select right sheet
		my ($mr, $row_max ) = $sheet->row_range();
		my ($mc, $col_max ) = $sheet->col_range();
		my $data_row = $mr +1;
		foreach my $row ($data_row .. $row_max) {
			my $record="C";     #insert the 'data_flag' value at front
			my $col = 0;
			foreach ( @Fields ) {
				if( match_field_label($Excel_labels, $_, "")==1 ){
					$col ++; next;
				}
				my $cell = $sheet->get_cell($row, $mc+$col);
				my $val="";
				$val  = $cell->value()  if($cell); 
				$val =~ s/[^\x20-\x7e]//g;
				$val =~ s/[\|\"\\]//g;

				return if($col == 0 and $val !~ /\d/); # No more data row

				$val = to2dp($val)    if ($col == 6 or $col ==7 or $col==19);
				if( $col >=15 && $col <=18 ) {
					$val=~ s/\s//g;
					if ($val =~ /^\d+$/ and $val > 40000 ){	
						$val = fmt_date($val);
					}
					elsif ($val !~ m{\d\d?[/-]\d\d?[/-]\d\d(\d\d)?$}) {	
						$val="";
					}
				}
				$val = handle_comment($val) if $col==20;
				$record .= "|$val";
	
				if ( $col == 12  ) {		# insert 'our_run_date' after carrier_code
					$record .= "|$ordate";
				}
				elsif ( $col == 19 and $val > 0  ) { # ignore comments field 
					$record .= "|";  last;
				}
				$col ++;
			}
			print "$record\n";
		}
	}
}


#------------------------------------------------------------
# Grab useful info from XLSX spreadsheet.
#
#   Two fields, 'data_flag' and 'our_run_date' are
# not from the spreadsheet.  
#       'data_flag' = 'C'
#       'our_run_date' is the recon file creation time.
#-----------------------------------------------------------
sub dump_Payor_Excel($$) {
	my $file = shift;
	my $Excel_labels = shift;

	my $ordate = $file;
	$ordate =~ s!^.*/!!;
	$ordate =~ s/^(\d{6}_\d{6}).*/$1/;
	my($dat, $hms) = split(/_/, $ordate);
	$dat =~ s!(\d\d)(\d\d)(\d\d)!$1/$2/$3!;
	$ordate = "$dat 00:00:00";

	my @Fields = get_fields($Excel_labels);

	my $excel_obj = Spreadsheet::ParseExcel->new();
	my $workbook = $excel_obj->parse($file);
	foreach my $sheet ( $workbook->worksheets()) {
		verify_Excel_fields($sheet,  $Excel_labels) or next;	#select right sheet
		my ($mr, $row_max ) = $sheet->row_range();
		my ($mc, $col_max ) = $sheet->col_range();
		my $data_row = $mr +1;
		foreach my $row ($data_row .. $row_max) {
			my $record="C";     #insert the 'data_flag' value at front
			my $col = 0;
			foreach ( @Fields ) {
				if( match_field_label($Excel_labels, $_, "")==1 ){
					$col ++; next;
				}
				my $cell = $sheet->get_cell($row, $mc+$col);
				my $val="";
				$val = $cell->value()  if($cell); 

				return if($col == 0 and $val !~ /\d/); # No more data row

				$val = to2dp($val)    if ($col== 6 or $col==7 or $col==68);
				if( $col>=65 && $col<=67){
					$val=~ s/\s//g;
					if ($val =~ /^\d+$/ and $val > 40000 ){	
						$val = fmt_date($val);
					}
					elsif ($val !~ m{\d\d?[/-]\d\d?[/-]\d\d(\d\d)?$}) {	
						$val="";
					}
				}
				$val = handle_comment($val) if $col==69;
				$record .= "|$val";

				#---------------------------------------------
				# insert some field which not appear in spreadsheet
				#---------------------------------------------
				if ( $col == 14  ) {	# insert 'our_run_date' after carrier_code
					$record .= "|$ordate";
				}
				elsif ( $col == 67  ) {	# insert empty field for 'Prior Ins Date' 
					$record .= "|";
				}

				$col ++;
			}
			print "$record\n";
		}
	}
}


#-----------------------------------------------
# Find out the Excel is a Payor/Recon report
#-----------------------------------------------
sub tell_Excel_report_type($) {
	my $file = shift;

	my $ret;
	my $excel_obj = Spreadsheet::ParseExcel->new();
	my $workbook = $excel_obj->parse($file);
	foreach my $sheet ( $workbook->worksheets()) {
		my ($mc, $col_max ) = $sheet->col_range();
		if ( $col_max > 5 ) {
			if(verify_Excel_fields($sheet, \%Recon_Excel_labels)) { 
				$ret = "recon"; last;
			}
			if(verify_Excel_fields($sheet, \%Payor_Excel_labels)) { 
				$ret = "payor"; last;
			}
		}
	}
	$ret;
}


#-----------------------------------------------
# Find out the Xlsx is a Payor/Recon report
#-----------------------------------------------
sub tell_xlsx_report_type($) {
	my $file = shift;

	my $ret;
	my $excel_obj = Spreadsheet::XLSX->new($file, undef);
	foreach my $sheet (@{$excel_obj->{Worksheet}}) {
		my $col_max = $sheet->{MaxCol};
		if ( $col_max > 5 ) {
			if( verify_xlsx_fields($sheet, \%Recon_Excel_labels)) { 
				$ret = "recon"; last;
			}
			if( verify_xlsx_fields($sheet, \%Payor_Excel_labels)) { 
				$ret = "payor"; last;
			}
		}
	}
	$ret;
}




#----------------------------------------------------
# Script starts here
#----------------------------------------------------

my $input = $ARGV[0];
exit 1 if ! $input;


my $file_type = qx(/usr/bin/file $input);

if( $file_type =~ /Zip\s/ ) {
	my $rtype = tell_xlsx_report_type($input);
	if ($rtype =~ /recon/i ) {
		dump_Recon_Xlsx($input, \%Recon_Excel_labels);
	} elsif ($rtype =~ /payor/i ) {
		dump_Payor_Xlsx($input, \%Payor_Excel_labels);
	}
	else {
		print STDERR "Unknow report type: $input\n";
		exit 1;
	}
}
elsif( $file_type =~ /Office\s+Document/i ) {
	my $rtype = tell_Excel_report_type($input);
	if ($rtype =~ /recon/i ) {
		dump_Recon_Excel($input, \%Recon_Excel_labels);
	}
	elsif ($rtype =~ /payor/i ) {
		dump_Payor_Excel($input, \%Payor_Excel_labels);
	}
	else {
		print STDERR "Unknow report type: $input\n";
		exit 1;
	}
}

exit 0;

